/**
 * 
 */
package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import entity.*;
import utils.DBHelper;

/**
 * @author ZYS
 *
 */
public class AnnouncementDAO {
	private AnnouncementDAO() {
	}

	public static AnnouncementDAO getInstance() {
		return new AnnouncementDAO();
	}
	/**
	 * 得到公告总数
	 *
	 */
	public int getTotal() {
		int total = 0;
		try {

			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "select count(*) from announcement";

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}

			System.out.println("total:" + total);

			DBHelper.closeConnection(c, s, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return total;
	}
	/**
	 * 新增主题
	 *
	 */
	public void add(Announcement announcement) {

		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "insert into announcement values(null,?)";
			PreparedStatement ps = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		
			ps.setString(1, announcement.getContent());
			
			ps.execute();
			
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int a_id =  rs.getInt(1);
				announcement.setA_id(a_id);
			}

			DBHelper.closeConnection(c, ps, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 修改公告内容
	 *
	 */
	public void update(Announcement announcement) {
		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "update announcement set content= ? where a_id = " + announcement.getA_id();
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setString(1, announcement.getContent());

			ps.execute();
		
			DBHelper.closeConnection(c, ps, null);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 删除公告
	 *
	 */
	public void delete(int a_id) {
		try {

			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "delete from announcement where a_id = " + a_id;

			s.execute(sql);

			DBHelper.closeConnection(c, s, null);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 通过公告id得到公告对象
	 *
	 */
	public Announcement get(int a_id) {
		Announcement announcement = null;
		try {
			
			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "select * from announcement where a_id = " + a_id;

			ResultSet rs = s.executeQuery(sql);

			while (rs.next()) {

				announcement = new Announcement();
				
				String content = rs.getString("content");
				
				announcement.setA_id(a_id);
				announcement.setContent(content);
				
			}

			DBHelper.closeConnection(c, s, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return announcement;
	}
	
	/**
	 * 通过公告id得到公告名称
	 *
	 */
	public String get_content(int a_id) {
		String content = "";
		try {
			
			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "select * from announcement where a_id = " + a_id;

			ResultSet rs = s.executeQuery(sql);

			while (rs.next()) {
				content = rs.getString("content");
			}

			DBHelper.closeConnection(c, s, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return content;
	}
	
	
	/**
	 * 得到公告列表
	 *
	 */
	public List<Announcement> list() {
		return list(0, Short.MAX_VALUE);
	}
	/**
	 * 得到从start开始count个公告
	 *
	 */
	public List<Announcement> list(int start, int count) {
		List<Announcement> announcements = new ArrayList<Announcement>();

		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "select * from announcement order by a_id limit ?,? ";

			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, start);
			ps.setInt(2, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Announcement announcement = new Announcement();
				int a_id = rs.getInt("a_id");
				String content = rs.getString("content");
			
				announcement.setA_id(a_id);
				announcement.setContent(content);
				
				announcements.add(announcement);
			}
			DBHelper.closeConnection(c, ps, rs);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return announcements;
	}
	/**
	 * 得到所有的
	 * @return
	 */
	public static List<Announcement> getAll(){
		List<Announcement> announcementlist = new ArrayList<Announcement>();
		try {
			Announcement announcement = null;
			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "select * from announcement";

			ResultSet rs = s.executeQuery(sql);

			while (rs.next()) {

				announcement = new Announcement();
				int a_id = rs.getInt("a_id");
				String content = rs.getString("content");				
				announcement.setA_id(a_id);
				announcement.setContent(content);
				announcementlist.add(announcement);
			}

			DBHelper.closeConnection(c, s, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return announcementlist;
		
	}
}
